﻿using System;
using System.Data;
using System.Data.OleDb;
using System.Data.Odbc;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Text;
using System.IO;
using System.Data.SqlClient;


namespace backoffice
{
    public class backofficecomponent
    {
        
        private int _MinKassa;
        public int MinKassa
        {
            get { return _MinKassa; }
            set { _MinKassa = value; }
        }

        private string _UploadDirectory = "backoffceupload";
        public string UploadDirectory
        {
            get { return _UploadDirectory; }
            set { _UploadDirectory = value; }
        }

        private string _ConnectionString = "Data Source=ARTHAS\\SQLEXPRESS;Initial Catalog=eschema;Persist Security Info=True;User ID=sa;Password=diablo";
        public string SQLConnectionString
        {
            get { return _ConnectionString; }
            set { _ConnectionString = value; }
        }

        private string _path ;
        public string path
        {
            get { return _path; }
            set { _path = value; }
        }
        
        private int _Interval=-999;
        public int Interval
        {
            get { return _Interval; }
            set { _Interval = value; }
        }

        public void SetFullPath(string ServerMapPath,string filename)
        {
            this.path = ServerMapPath + @"\" + filename;
        }
     

        public void SetInterval(backofficereport one, backofficereport two)
        {
            this._Interval = Math.Abs( Convert.ToInt16(one.TimeUnit.Hours - two.TimeUnit.Hours));
        }
        private int _CustomerPerCashBox;
        public int CustomerPerCashBox
        {
            get { return _CustomerPerCashBox; }
            set { _CustomerPerCashBox = value; }
        }
        private string _ExcelSheetName;
        public string ExcelSheetName
        {
            get { return _ExcelSheetName; }
            set { _ExcelSheetName = value; }
        }

        private DataTable _ExcelSheetTable;
        public DataTable ExcelSheetTable
        {
            get { return _ExcelSheetTable; }
            set { _ExcelSheetTable = value; }
        }


        public int GetCashBoxPerInterval(backofficereport report)
        {
            if (this._Interval == -999)
                return 0;
            else
            {
                int NumberOfCashbox = (int)Math.Ceiling(Convert.ToDecimal(report.CustomersTotal) / Convert.ToDecimal(this._CustomerPerCashBox));

                if (NumberOfCashbox <= this._MinKassa)
                    return this._MinKassa;
                else
                    return (int)NumberOfCashbox;
            }
        }

        private int _ExcelFileRowCount;
        public int ExcelFileRowCount
        {
            get { return _ExcelFileRowCount; }
            set { _ExcelFileRowCount = value; }
        }

        public static StringBuilder JSinit()
        {
            StringBuilder scr = new StringBuilder();
            scr.Append("<script type='text/javascript'>");
            scr.Append("function drawChart() {");
            scr.Append("var data = new google.visualization.DataTable();");
            scr.Append("data.addColumn('string', 'Tid');");
            scr.Append("data.addColumn('number', 'Antal Kassa');");
            return scr;
        }
        public static StringBuilder JSNumberOfRows(int NumberOfIntervals, StringBuilder JavaScript)
        {

            JavaScript.Append("data.addRows(" + NumberOfIntervals.ToString() + ");");
            return JavaScript;
        }
        public static StringBuilder JSAddStringRow(int x, int y, string value, StringBuilder JavaScript)
        {

            JavaScript.Append("data.setValue(" + x.ToString() + "," + y.ToString() + "," + "'" + value + ":00'" + ");");
            return JavaScript;
        }
        public static StringBuilder JSAddIntRow(int x, int y, string value, StringBuilder JavaScript)
        {

            JavaScript.Append("data.setValue(" + x.ToString() + "," + y.ToString() + "," + value + ");");
            return JavaScript;
        }
        public StringBuilder GoogleChartDataTableInitializer()
        {
            return GoogleChartDataTableInitializer("drawChart");

        }
        public StringBuilder GoogleChartDataTableInitializer(int Prefix)
        {
            return GoogleChartDataTableInitializer("drawChart_"+Prefix.ToString());

        }
        
        private StringBuilder GoogleChartDataTableInitializer(string Function)
        {
            StringBuilder scr = new StringBuilder();
         //   scr.Append("<script type='text/javascript'>");  //remove this, add HtmlControll innerText
            scr.Append(Environment.NewLine);
            scr.Append("function "+Function+"() {");
            scr.Append(Environment.NewLine);
            scr.Append("var data = new google.visualization.DataTable();");
            scr.Append(Environment.NewLine);
            return scr;
        
        }
        public StringBuilder GoogleChartTableColumneGenerator(string ColumnType, string ColumnName)
        {
            StringBuilder Script = new StringBuilder();
            Script.Append("data.addColumn('"+ColumnType+"','"+ColumnName+"');");
            Script.Append(Environment.NewLine);
            return Script;
        }
        public StringBuilder GoogleChartTableNumberOfRows(int NumberOfIntervals)
        {
            StringBuilder Script = new StringBuilder();

            Script.Append("data.addRows(" + NumberOfIntervals.ToString() + ");");
            Script.Append(Environment.NewLine);
            return Script;

        }
        public string GoogleChartTableAddRow(string Xvalue, string Yvalue, string value, bool IsQouted)
        {
            StringBuilder temp = new StringBuilder();
            if (IsQouted)
            {
                temp.Append("data.setValue(" + Xvalue.ToString() + "," + Yvalue.ToString() + "," + "'" + value + ":00'" + ");");
            }
            else {
                temp.Append("data.setValue(" + Xvalue.ToString() + "," + Yvalue.ToString() + "," + value + ");");
            }
            return temp.Append(Environment.NewLine).ToString();
            
        }

        public StringBuilder GoogleChartVisualizationChartObjectBuilder(string HtmlId,string Title,int width,int Height)
        {
            StringBuilder Script = new StringBuilder();
            Script.Append(" var chart = new google.visualization.AreaChart(document.getElementById('" + HtmlId + "'));");
            Script.Append(Environment.NewLine);
            Script.Append("chart.draw(data, { width:" + width.ToString() + ",height:" + Height.ToString() + ", legend: 'bottom', title: '" + Title + ": antal kassa' });");
            return Script.Append(Environment.NewLine);
        }
        public StringBuilder GoogleChartFinilazer()
        {
            StringBuilder Script = new StringBuilder();
            Script.Append("}");
            return Script.Append(Environment.NewLine);
            //return Script.Append("</script>");
        }
        public string GoogleCallbackFunctionGenerator()
        {
            return GoogleCallbackFunctionGenerator("drawChart");
        }
        public string GoogleCallbackFunctionGenerator(string functionname)
        {
            return GoogleCallbackFunctionGenerator("drawChart",0);
        }

        public string GoogleCallbackFunctionGenerator(string FunctionName,int Prefix)
        {
            StringBuilder sc = new StringBuilder();
            sc.Append("google.setOnLoadCallback(" + FunctionName+"_" + Prefix.ToString()+");");
            return sc.Append(Environment.NewLine).ToString();

        }

        public static StringBuilder JSFinalize(StringBuilder JavaScript)
        { 
            JavaScript.Append(" var chart = new google.visualization.AreaChart(document.getElementById('chart_div'));");
            JavaScript.Append("chart.draw(data, { width: 1500, height: 240, legend: 'bottom', title: 'antal kassa' });");

            JavaScript.Append(" var chart = new google.visualization.AreaChart(document.getElementById('chart_div2'));");
            JavaScript.Append("chart.draw(data, { width: 800, height: 240, legend: 'bottom', title: 'antal kassa' });");

            JavaScript.Append(" var table = new google.visualization.Table(document.getElementById('table_div'));");
            JavaScript.Append(" table.draw(data, null); }");
            
            JavaScript.Append("</script>");
            return JavaScript;
           
        }

        public string GoogleChartDraw(DataTable table,int prefix,string controlid,string title)
        {
           
            return new StringBuilder(
                this.GoogleChartDataTableInitializer(prefix).ToString() +
                this.GoogleChartTableColumneGenerator("string", "Tid").ToString() +
                this.GoogleChartTableColumneGenerator("number", "Antal Kassa").ToString() +
                this.GoogleChartTableNumberOfRows(TableRows(table)).ToString() +
                this.GoogleChartAddDataRowsFromTable(table).ToString() +
                this.GoogleChartVisualizationChartObjectBuilder(controlid, title, 600, 300).ToString() +
                this.GoogleChartFinilazer().ToString()
                ).ToString();
        }
  

        private StringBuilder GoogleChartAddDataRowsFromTable(DataTable table)
        {
            StringBuilder src = new StringBuilder();
            backofficereport[] reports = backofficereport.InitArray(this.TableRows(table));
            reports = this.FillReporterArrayFromDataBaseTable(table);
            try
            {
               if(this.TableRows(table)>0)
                    this.SetInterval(reports[1], reports[0]);
            }
            catch (Exception){ 
                    
            }
            for(int i = 0;i<this.TableRows(table);i++)
            {
                src.Append(this.GoogleChartAddSingleDataRowFromBackOfficeReport(i,reports[i]));
            }
                    
            return src;
        }

        private StringBuilder GoogleChartAddSingleDataRowFromBackOfficeReport(int x,backofficereport r)
        {
            StringBuilder sc = new StringBuilder();
            string temp = this.GoogleChartTableAddRow(x.ToString(), "0", r.TimeUnit.Hours.ToString(), true).ToString();
            sc.Append(temp);
            temp = this.GoogleChartTableAddRow(x.ToString(), "1", this.GetCashBoxPerInterval(r).ToString(), false).ToString();
            sc.Append(temp);
            return sc;
        }
        private StringBuilder GoogleChartCombiner(StringBuilder Init,StringBuilder cols,StringBuilder rownum, StringBuilder rows,StringBuilder visualobject, StringBuilder finalize)
        {
          return new StringBuilder(Init.ToString()+cols.ToString()+rownum.ToString() + rows.ToString()+visualobject.ToString() + finalize.ToString());
        }

        private int TableRows(DataTable table)
        {
            return table.Rows.Count;
        }

        public DataTable ExcelReader()
        {
            return this.ExcelReader(this.path);            
        }
        public DataTable ExcelReader(string path)
        {
            DataTable dt = new DataTable();
            string temp;  //storing table name and queries
            if (File.Exists(path))
            {
            OleDbConnection ExcelConnector = new OleDbConnection();
                DataTable ExcelSheet = new DataTable(); 
                ExcelConnector.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";Extended Properties=" + "'Excel 8.0;HDR=No;IMEX=1'";
                ExcelConnector.Open();
                dt = ExcelConnector.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,null);
                if (dt.Rows.Count > 0)
                    {
                        temp = dt.Rows[0]["TABLE_NAME"].ToString();
                        temp = temp.Replace("$", "");
                        this.ExcelSheetName = temp;    
                        temp = string.Format("SELECT * FROM [{0}$] ", temp);
                        OleDbDataAdapter Commander = new OleDbDataAdapter(temp, ExcelConnector);
                        
                       Commander.Fill(ExcelSheet);
                       this._ExcelFileRowCount = ExcelSheet.Rows.Count;
                    
                        // useed if we ever need to read more than 1 sheet. 
                        //for (int i = 0; i < dt.Rows.Count; i++)
                        //{
                        //    temp = dt.Rows[i]["TABLE_NAME"].ToString();
                        //    temp = temp.Replace("'", "");
                        //    temp = temp.Replace("$", "");
                        //}
                    }
                this.ExcelSheetTable = ExcelSheet;
                return ExcelSheet;
            }
            else 
            {
                return null;
            }
        }

  

        public void ExcelRecorder(backofficereport [] row)
        {
            for (int i = 1; i < row.Length; i++)
                row[i].InsertDb();                 
        }
        public backofficereport[] FillReporterArray()
        { 
            return FillReporterArray(this.ExcelSheetTable);
        }

        public backofficereport[] FillReporterArray(DataTable sheet)
        {
            backofficereport[] row = backofficereport.InitArray(sheet.Rows.Count);
            for (int i = 1; i < this.ExcelFileRowCount; i++)
            {
                row[i].CustomerId = 3;  //FIX THIS SHIT, need to get it from session or something. ask hooman
                row[i].Date = DateTime.Parse(sheet.Rows[i]["F1"].ToString());
                row[i].TimeUnit = TimeSpan.Parse(sheet.Rows[i]["F2"].ToString());
                row[i].CustumersForEachCashBox = Convert.ToInt32(sheet.Rows[i]["F3"].ToString());
                row[i].CustomersTotal = Convert.ToInt32(sheet.Rows[i]["F4"].ToString());
            }
            return row; 
            
        }
        public backofficereport[] FillReporterArrayFromDataBaseTable(DataTable sheet)
        {
            backofficereport[] row = backofficereport.InitArray(sheet.Rows.Count);
            for (int i = 1; i < sheet.Rows.Count; i++)
            {
                row[i].CustomerId = 3;  //FIX THIS SHIT, need to get it from session or something. ask hooman
                row[i].Date = DateTime.Parse(sheet.Rows[i]["DateReport"].ToString());
                row[i].TimeUnit = TimeSpan.Parse(sheet.Rows[i]["TimeUnit"].ToString());
                row[i].CustumersForEachCashBox = Convert.ToInt32(sheet.Rows[i]["CustumersForEachCashBox"].ToString());
                row[i].CustomersTotal = Convert.ToInt32(sheet.Rows[i]["CustomersTotal"].ToString());
            }
            return row;

        }

        public bool FileUpload(FileUpload uploader, string path)
        {
            if (uploader.HasFile)
            {
                if (File.Exists(path))
                {
                    File.Delete(path);
                }
                uploader.PostedFile.SaveAs(path);
                return (File.Exists(path)) ? true : false;
             }
            else {

                return false;
            }
        }
        public int GetNumberOfDateGroups()
        {
            return GetNumberOfDateGroups(5);
        }

        public int GetNumberOfDateGroups(int count)
        {
            int counter=0;
            SqlConnection con = new SqlConnection(this.SQLConnectionString);
            con.Open();
            SqlCommand cmd = new SqlCommand();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "USPSelectDateByDateGroup";
            cmd.Parameters.Add("@CustomerId", SqlDbType.Int).Value =3; //Fix this
            cmd.Parameters.Add("@ReturnCount", SqlDbType.Int).Value = 1;
            cmd.Parameters.Add("@Rowcount", SqlDbType.Int).Value = count;
         
            cmd.Connection = con;
            using (System.Data.SqlClient.SqlDataReader reader = cmd.ExecuteReader())
            {
                while (reader.Read())
                {
                    counter = Convert.ToInt32(reader["NumberOfGroups"].ToString());
                }
                return counter;
            }
            
        }

        public DataTable GetDateGroups()
        { 
            return   GetDateGroups(5);      
        }
        public DataTable GetDateGroups(int count)
        {
           
            SqlConnection con = new SqlConnection(this.SQLConnectionString);
            con.Open();
            SqlCommand cmd = new SqlCommand();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "USPSelectDateByDateGroup";
            cmd.Parameters.Add("@CustomerId", SqlDbType.Int).Value = 3; //Fix this
            cmd.Parameters.Add("@ReturnCount", SqlDbType.Int).Value = 0;
            cmd.Parameters.Add("@Rowcount", SqlDbType.Int).Value = count;
            cmd.Connection = con;
            DataTable table = new DataTable();
            table.Load(cmd.ExecuteReader(CommandBehavior.CloseConnection));
            return table;
           
        }

        public string[] GetDateGroupsArray(DataTable table)
        {
            string [] date = new string[table.Rows.Count];

            for (int i = 0; i < table.Rows.Count; i++)
            { 
              date [i] = table.Rows[i]["grouped"].ToString();
            }
            return date;
            
        }

        public DataTable GetSingleDateGroup(string group)
        {
            DataTable table = new DataTable();
            SqlConnection con = new SqlConnection(this.SQLConnectionString);
            con.Open();
            SqlCommand cmd = new SqlCommand();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "USPSelectByDate";
            cmd.Parameters.Add("@CustomerId", SqlDbType.Int).Value = 3; //Fix this
            cmd.Parameters.Add("@DateReport", SqlDbType.DateTime).Value = DateTime.Parse(group);
            cmd.Connection = con;
            table.Load(cmd.ExecuteReader(CommandBehavior.CloseConnection));
            return table;
            
        }


        public System.Web.UI.Control HtmlControlGenerator(string Control, string Id)
        {
            return HtmlControlGenerator(Control, Id, null,null,null);
        }
        
         public System.Web.UI.Control HtmlControlGenerator(string Control, string Id,int Prefix)
        {
            HtmlGenericControl HtmlControl = new HtmlGenericControl(Control);
            HtmlControl.ID = Id + "_" + Prefix.ToString();
            return HtmlControl;
        }
        

        public System.Web.UI.HtmlControls.HtmlGenericControl HtmlControlGenerator(string Control, string Id, string HtmlAttribute,string Val,string InnerHtml)
        {
            HtmlGenericControl HtmlControl = new HtmlGenericControl(Control);
            HtmlControl.ID = Id;
            if (HtmlAttribute != null&&Val != null)
            {
                HtmlControl.Attributes.Add(HtmlAttribute, Val);
            }
            if (InnerHtml != null)
                HtmlControl.InnerText = InnerHtml;
            return HtmlControl;
        }

        

    }
}
